********************************************************************************
*Project:  	Swiss Trade During the Covid-19 Pandemic: An Early Appraisal	
*Authors:	KB, SL, VP, PW											
*This File: Clean Classifications of Federal Cusomts Authority
*Version: 	vFinal						
*Date: 		2020-09-01					
********************************************************************************

********************************************************************************
*1. Import & Clean Main FCA-Classifications Files
********************************************************************************

*Codes Handelspartner
import excel "${root}\data\FCA_Classifications\Codes_Handelspartner_v2018.xlsx", 	///
sheet("deutsch") firstrow case(lower) clear

drop d j m n o p q r s 

rename b land_id
rename c land_str
rename aktuellerzustand land_code
rename e valid_start
rename f valid_end
rename history land_str2
rename h land_id2
rename i land_code2
rename k valid_start2
rename l valid_end2

drop if land_str=="Land"


foreach var in valid_start valid_end valid_start2 valid_end2 {
	replace `var'=substr(`var', -4, .)
	destring `var', replace
}

preserve
keep *2
drop if land_code2==""
drop if valid_end2<2002
duplicates drop land_id2 land_code2, force
duplicates drop land_id2, force
rename *2 *
compress
saveold "${root}\temp\codes_land_history_temp.dta", replace version(13)
restore 

preserve
drop *2
drop if land_code==""
compress
saveold "${root}\temp\codes_land_current_temp.dta", replace version(13)
restore 

use "${root}\temp\codes_land_current_temp.dta", clear
append using "${root}\temp\codes_land_history_temp.dta"

destring land_id, replace
sort land_id

rename land_code land_iso2
duplicates tag land_id, gen(dup)
bysort land_id: egen min_end=min(valid_end)
bysort land_id: egen max_end=max(valid_end)
drop if dup==1 & valid_end!=max_end & max_end==2999
drop min_end max_end dup
duplicates tag land_id, gen(dup)
drop if dup==1 & land_str=="Somalia, Bundesrepublik"
drop dup

drop valid*
order land_id land_str land_iso2
compress 
saveold "${root}\temp\codes_land.dta", replace version(13)

*Codes Handelspartner vom SECO
import delimited ${root}\data\FCA_Classifications\HP_Codes_Seco.csv,			///
delimiter(";") bindquote(nobind) stripquote(yes) clear 

rename v1 HP
rename v2 land_str
drop if HP=="HP_code"
drop if HP=="tot"

destring HP, replace
clonevar land_id=HP

merge 1:1 land_id using "${root}\temp\codes_land.dta"
drop _merge

replace land_iso2="XXX" if land_iso2==""
rename land_iso2 land_iso2_SECO

saveold "${root}\temp\codes_HP_SECO.dta", replace version(13)

*Codes Warenart
import excel "${root}\data\FCA_Classifications\waren_warenart.xlsx", 				///
sheet("Warenart") firstrow case(lower) clear

rename nummer waren_art_id
rename text waren_art_str
drop if waren_art_id==""
drop if waren_art_str==""

drop gültig*

sort waren_art_id
order waren_art_id waren_art_str
compress 
saveold "${root}\temp\codes_warenart.dta", replace version(13)

*Codes Waren-Verwendungszweck
import excel "${root}\data\FCA_Classifications\waren_verwendungszweck.xlsx", 		///
sheet("Verwendungszweck") firstrow case(lower) clear

rename nummer waren_vwz_id
rename text waren_vwz_str
drop if waren_vwz_id==""
drop if waren_vwz_id==" "
drop if waren_vwz_str==""

drop gültig*

sort waren_vwz_id
order waren_vwz_id waren_vwz_str
compress 
saveold "${root}\temp\codes_verwendungszweck.dta", replace version(13)

*Codes Waren-Tarifnummern
import excel "${root}\data\FCA_Classifications\waren_tarifnummern.xlsx", 			///
sheet("Tarifnummer") firstrow case(lower) clear

rename nummer waren_tnr_id
rename text waren_tnr_str
drop if waren_tnr_id==""
drop if waren_tnr_id==" "
drop if waren_tnr_str==""

drop gültig*

sort waren_tnr_id
order waren_tnr_id waren_tnr_str
compress 
saveold "${root}\temp\codes_tarifnummern.dta", replace version(13)

*Link Tarifnummer-Verwendungszweck
import excel "${root}\data\FCA_Classifications\waren_tarifnummern_verwendungszweck.xlsx", 			///
sheet("DE") firstrow case(lower) clear

rename verwendungszweck waren_vwz_id
rename b waren_vwz_str
rename tarifnummer waren_tnr_id
rename schlüssel waren_tnr_key
rename e handel_typ
rename f date_start
rename stand01012020 date_end

drop if waren_vwz_str=="Bezeichnung"

gen valid_year_start=substr(date_start, -4, .)
gen valid_year_end=substr(date_end, -4, .)

destring valid_year_*, replace
drop date_*

drop if valid_year_end<2002

*SIMPLIFY DATASET: KEEP ONLY 1 WAREN-ID TNR PAIR
*DROP IMPORTS IF waren_art_id & waren_tnr_id DUPLICATE (1 E, 1 I) 
duplicates drop waren_vwz_id waren_tnr_id, force
drop handel_typ

gen waren_vwz_id_2d=substr(waren_vwz_id, 1, 2)
duplicates drop waren_vwz_id_2d waren_tnr_id, force 
gen lwvi=length(waren_vwz_id)

gen waren_vwz_str2t=waren_vwz_str if waren_tnr_id==""
bysort waren_vwz_id_2d: egen waren_vwz_str2=mode(waren_vwz_str2t)
drop waren_vwz_str2t waren_vwz_str
rename waren_vwz_str2 waren_vwz_str

drop if lwvi==2
drop lwvi waren_vwz_id

order waren_vwz_str
drop if waren_tnr_id==""
duplicates tag waren_tnr_id, gen(dup)
tab dup


sort waren_vwz_id
order waren_vwz_id waren_vwz_str waren_tnr_id waren_tnr_key //handel_typ date_start date_end
compress 
saveold "${root}\temp\link_tarifnummern_verwendungszweck.dta", replace version(13)

*Link Tarifnummer-Warenart
import excel "${root}\data\FCA_Classifications\waren_tarifnummern_warenart.xlsx", 	///
sheet("DE") firstrow case(lower) clear

rename warenart waren_art_id
rename b waren_art_str
rename tarifnummer waren_tnr_id
rename schlüssel waren_tnr_key
rename e handel_typ
rename f date_start
rename stand01012020 date_end

drop if waren_art_str=="Bezeichnung"

gen valid_year_start=substr(date_start, -4, .)
gen valid_year_end=substr(date_end, -4, .)

destring valid_year_*, replace
drop date_*

drop if valid_year_end<2002

*SIMPLIFY DATASET: KEEP ONLY 1 WAREN-ID TNR PAIR
*DROP IMPORTS IF waren_art_id & waren_tnr_id DUPLICATE (1 E, 1 I) 
duplicates drop waren_art_id waren_tnr_id, force
drop handel_typ

gen waren_art_id_3d=substr(waren_art_id, 1, 4)
duplicates drop waren_art_id_3d waren_tnr_id, force 
drop waren_art_id
gen lwai=length(waren_art_id_3d)
drop if lwai==2
drop lwai

gen waren_art_str2t=waren_art_str if waren_tnr_id==""
bysort waren_art_id: egen waren_art_str2=mode(waren_art_str2t)
drop waren_art_str2t waren_art_str
rename waren_art_str2 waren_art_str
order waren_art_str
drop if waren_tnr_id==""
duplicates tag waren_tnr_id, gen(dup)
tab dup

sort waren_art_id
order waren_art_id waren_art_str waren_tnr_id waren_tnr_key valid_year_* //handel_typ
compress 
saveold "${root}\temp\link_tarifnummern_warenart.dta", replace version(13)




